<?php
error_reporting(0);
$server = "172.21.147.31";
$connectionInfo = array( "Database"=>"ss2g6", "UID"=>"ss2g6", "PWD"=>"group6" , 'ReturnDatesAsStrings'=> true );
$conn = sqlsrv_connect( $server, $connectionInfo );

if ($conn == false){
	 die( print_r( sqlsrv_errors(), true));
}

if(isset($_GET['update'])){
    $updateSideEffectName = $_GET['name'];
    $sql = "SELECT * FROM SideEffect WHERE name = '$updateSideEffectName'";
    $result = sqlsrv_query( $conn, $sql);
    $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

    $updateSideEffectName = $row['name'];
}

if(isset($_POST['Update'])){
    $updateSideEffectName = $_POST['name'];
    $updateSideEffectNameOld = $_POST['nameOld'];
    
    $sql = "UPDATE SideEffect SET name = '$updateSideEffectName' WHERE name = '$updateSideEffectNameOld'";
    $result = sqlsrv_query( $conn, $sql);

    $confirm = "SideEffect information updated.";
}

if(isset($_POST['Insert'])) {
    $name = $_POST['name'];
	if($name == "" )
    {
        $validation_error = true;
    }
	else {
		$validation_error = false;
	}
	
    $sql = "INSERT INTO sideEffect VALUES ('$name')";
	
	if($validation_error == false){
    $params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result = sqlsrv_query( $conn, $sql, $params, $options );
	if ($result == false){
		$error_insert = true;
	}
	else{
		$error_insert = false;
	}
	
    $confirm = "The side effect information is inserted.";
	}
	else{
		$confirm = "";
	}
}

if(isset($_GET['delete'])){
	$name = $_GET['name'];
	$sql = "DELETE FROM SideEffect WHERE name = '". $name ."'";
	$params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result = sqlsrv_query( $conn, $sql, $params, $options );
	if($result == false){
		$error = true;
	}
	else {
		$error = false;
	}
	/*$rowsAffected = sqlsrv_rows_affected ($result);
	if ($rowsAffected === false){
		 $error = true;     
	}*/	
}

if (isset($_POST['submit'])){
    $sql1 = $_POST['query'];
}
else
{
    $sql = "SELECT * FROM SideEffect";
}

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );
?>

<? include 'html_head.php' ?>
    <? include 'header.php' ?>

    <div class="container-fluid">
      <div class="row-fluid">
        <div class="span2">
            <? include 'sidebar.php' ?>
        </div><!--/span-->
        <div class="span9">
        <h2>SideEffect</h2>
        <hr>
		<?
        if(isset($_GET['delete'])){
			if ($error){ ?>
				<div class="alert alert-error">
					<? echo "Error in deleting. Foreign Key constraint violated.<br />";?>
				</div>
				
			<? 
			} 
			else { ?>
				<div class="alert alert-success">
				<? echo "Data deleted successfully."?>
				</div>
				
			<?
			} 
		}?>
		
		<? if (isset($_POST['Update'])){ ?>
		    <div class="alert alert-success">
			    <? echo $confirm;?>
			</div>
       <? } ?>
        
        <?
			if(isset($_POST['Insert'])){
				if($validation_error){ ?>
					<div class="alert alert-error">
						<? echo "Name of the ingredient cannot be empty.<br />";?>
					</div>
				<?
				}
				elseif (isset($error_insert) && $error_insert){ ?>
					<div class="alert alert-error">
						<? echo "Data cannot be inserted, foreign key constraint or triggers is violated <br />";?>
					</div>
					
				<? 
				} 
				else { ?>
					<div class="alert alert-success">
					<? echo $confirm;?>
					</div>
					
				<?
				} 
			}?>
        <?
        
		if(sqlsrv_has_rows($stmt))
		{		
			$row_count = sqlsrv_num_rows($stmt);
            $colnames = sqlsrv_field_metadata($stmt)
		?>
		
		<table class="table table-striped table table-condensed">
		
		<thead>
			<tr>
			  <?
  					echo "<th>SideEffect Name</th>";
                ?>
			</tr>	
			</thead>
		<tbody>
		<?
			while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
        ?>
			<tr>
				<?
					foreach ($colnames as &$colname){
					$cellname = $colname['Name'];
					echo "<td>";
					if ($colname['Type'] == 93){
						$temp_date = explode(" ", $row[$cellname]);
						echo $temp_date[0];
					}
					else{
						echo $row[$cellname];
					}
					echo "</td>";
					}
					$name = urlencode($row['name']);
					echo "<td><a href='side.php?delete=true&name=" . $name . "'>Delete</a></td>";
					echo "<td><a href='side.php?update=true&name=" . $name . "#updates'>Update</a></td>";
				?>
			</tr>
		  
		<?
			}
		?>
		</tbody>
		</table>
		<?
		}
		else{
			echo "<g2>No records.</h2>";
		}
		?>
          <div class="row-fluid">
            <div class="span12">
            <? if(isset($_GET['update'])){ ?>
            <div>
            <a name="updates"></a><h3>Update SideEffect information:</h3> 
                    <form method="post" action="side.php" class="well form-horizontal">
                    <input type="hidden" name="nameOld" value="<? echo $updateSideEffectName; ?>" />
                    <div class="control-group">
                        <label class="control-label">Name</label>
                        <div class="controls">
                            <input type="text" name="name" id="name" value="<? echo $updateSideEffectName; ?>"/>
                        </div>
                    </div>                    
                    <div class="form-actions">
                        <input type="submit" name="Update" value="Update"  class="btn btn-primary"/>
                    </div> 
                    </form>
            </div>

            <? } ?>
                          
              <h3>Create a new SideEffect information:</h3> 
                    <form method="post" action="side.php" class="well form-horizontal">
                    <div class="control-group">
                        <label class="control-label">Name</label>
                        <div class="controls">
                            <input type="text" name="name" id="name" placeholder="eg. Headache" />
                        </div>
                    </div>                    
                    <div class="form-actions">
                        <input type="submit" name="Insert" value="Insert"  class="btn btn-primary"/>
                    </div> 
                    </form>
              <h3>Enter your query:</h3>
                    <form method="post" action="custom.php" class="well form-inline">
                    <div class="control-group">
                        <div class="controls">
                            <textarea name="query" class="span10" id="textarea" rows="5" placeholder = 'SQL query'></textarea>
                        </div>
                    </div>
                    <p>
                    <p><input type="submit" name="submit" class="btn btn-primary" href="#" /></p>
                    </form>
                    
            </div><!--/span-->
          </div><!--/row-->
        </div><!--/span-->
      </div><!--/row-->

      <hr>

      <footer>
        <p>&copy; SS2 GROUP 6 </p>
      </footer>

    </div><!--/.fluid-container-->
  </body>
</html>